<?php
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

//Set properties
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(4)->setAutoSize(true);

$objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(23);
$objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getFill()->getStartColor()->setARGB('dadada');
$objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A3:C3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

// Add some data
$objPHPExcel->getActiveSheet()->getStyle('A1:C1')->getFont()->setName('Times New Roman');
$objPHPExcel->getActiveSheet()->getStyle('A1:C1')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(25);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:C1')->setCellValue("A1","Rata - Rata IKM Per Unsur");
$objPHPExcel->getDefaultStyle()->getFont()->setName('Calibri');
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A3', 'No')
            ->setCellValue('B3', 'Pertanyaan')
            ->setCellValue('C3', 'Nilai Unsur Pelayanan');

$a          = 3;
$no         = 0;
foreach ($laporan as $row) {
$a++;
$no++;
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A'.$a, $no)
            ->setCellValue('B'.$a, $row->PERTANYAAN)
            ->setCellValue('C'.$a, $row->NILAI);
}
$objPHPExcel->getActiveSheet()->getStyle("A4:C".($a+1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('B'.($a+1) . ':' . 'C'.($a+6))->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('C'.($a+3) . ':' . 'C'.($a+6))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.($a+1), '=CEILING(AVERAGE(C4:C' . $a . ')/4*100,0.01)');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B'.($a+1), 'IKM');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.($a+3), 'Kesimpulan');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.($a+4), 'Nilai IKM setelah dikonversi');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.($a+4), '=');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.($a+4), '=CEILING(AVERAGE(C4:C' . $a . ')/4*100,0.01)');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.($a+5), 'Mutu pelayanan');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.($a+5), '=');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.($a+5), '=IF(C' . ($a+1) . '>81.26,"A",IF(C' . ($a+1) . '>62.51,"B",IF(C' . ($a+1) . '>43.76,"C","D")))');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C'.($a+6), 'Kinerja Unit Pelayanan');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D'.($a+6), '=');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E'.($a+6), '=IF(C' . ($a+1) . '>81.26,"Sangat Baik",IF(C' . ($a+1) . '>62.51,"Baik",IF(C' . ($a+1) . '>43.76,"Kurang Baik","Tidak Baik")))');
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Rata_Rata_IKM_Per_Unsur');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Rata_Rata_IKM_Per_Unsur.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
